﻿﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace FCForms
{
    public class ExcelHelper
    {
        #region 员工工资表
        /// <summary>
        /// 导出员工工资表
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strSheetName"></param>
        /// <param name="oldColumnNamed"></param>
        /// <param name="newColumnNames"></param>
        /// <param name="title"></param>
        /// <param name="team"></param>
        public static void ExportByPersonSalary(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportBySalaryStream(dtSource, strHeaderText,strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportBySalaryStream(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames,  string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);

            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(2);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 12));
                        row1.CreateCell(0).SetCellValue(team+"项目班组劳务工人" + DateTime.Now.Year + "年" + DateTime.Now.Month + "月工资发放表");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 28 * 30;
                        //设置第5列宽度
                        sheet.SetColumnWidth(4, 18 * 256);
                        //设置第11列宽度
                        sheet.SetColumnWidth(11, 18 * 256);

                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 12));
                        
                        row2.CreateCell(0).SetCellValue("班组名称："+team+"                                                                                                                          编制日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
                        row2.Height = 14 * 30;

                        //最后一行
                        int num = Convert.ToInt32(dtSource.Rows.Count) + 3;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 12));
                       
                        IRow row3 = sheet.CreateRow(num);
                        row3.CreateCell(0).SetCellValue("总包单位项目经理审核：                    总包单位项目劳务管理员审核：                              劳务公司现场负责人：                        班组长：	                 ");
                        //row3.Height = 18 * 30;
                        //row3.GetCell(0).CellStyle = headStyle;
                    }
                    rowIndex = 3;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion


        #region 人员信息档案表
        public static void ExportByEmployeeEntryExit(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames,  team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 19));
                        row1.CreateCell(0).SetCellValue("人员信息档案表");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 28 * 30;
                         
                    }
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 人员花名册
        public static void ExportByEmployeeEntryExit2(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream2(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream2(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.1);
            sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B________公司务工人员（含队长、班组长、农民工）花名册&B\n" + HSSFHeader.FontSize((short)10) + "班组名称：&U  " + team.Split(',')[0] + "  &U";
            sheet.Header.Left = "              \n\n项目名称（全称）：__________";
            sheet.Header.Right = "               \n\n" + team.Split(',')[1] + "年 " + team.Split(',')[2] + "月";          
            sheet.Footer.Left = "申明：此表登记务工人员为我单\n位在该工程全部人数，情况属实。\n             ";
            sheet.Footer.Center = "班组长签字：___________ ；用工企业劳务员签字：___________，\n用工企业项目负责人（授权队长）签字：________；填表时间：________ \n 第&P页,共&N页";
            sheet.Footer.Right = "用工企业盖章：_________       \n           \n            ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(0);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 265);
                        }
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        //IRow row1 = sheet.CreateRow(0);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6));
                        //row1.CreateCell(0).SetCellValue("_____________公司务工人员（含队长、班组长、农民工）花名册");
                        //row1.GetCell(0).CellStyle = headStyle;

                        ////设置第一列宽度
                        //sheet.SetColumnWidth(0, 12 * 256);
                        //row1.Height = 28 * 30;

                    }
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 人员考勤表
        public static void ExportByEmployeeEntryExit3(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream3(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream3(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)0.9);
            //HSSFHeader head = new HSSFHeader();

            sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B__________公司务工人员（含队长、班组长、农民工）考勤表&B\n" + HSSFHeader.FontSize((short)10)+"班组名称：&U  " + team.Split(',')[0] + "  &U ";
            sheet.Header.Left = "              \n\n项目名称（全称）：__________";
            sheet.Header.Right = "               \n\n " + team.Split(',')[1] + "年 " + team.Split(',')[2] + "月";
            sheet.Footer.Left = "班组长签字：___________  \n             ";
            sheet.Footer.Center = "用工企业劳务员签字：_______，用工企业项目负责人（授权队长）签字：________；填表时间：________ \n 第&P页,共&N页";
            sheet.Footer.Right = "用工企业盖章：_________       \n                      ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            long totalCount = 0;
            for (var j = 0; j < dtSource.Rows.Count; j++)
            {
                DataRow row = dtSource.Rows[j];
                //}
                //foreach (DataRow row in dtSource.Rows)
                //{

                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow1 = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(0);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 3, 8));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 9, 33));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 34, 34));
                        //row1.GetCell(0).CellStyle = headStyle;
                        //row1.GetCell(1).CellStyle = headStyle;
                        //row1.GetCell(2).CellStyle = headStyle;
                        //row1.GetCell(3).CellStyle = headStyle;
                        //row1.GetCell(9).CellStyle = headStyle;
                        //row1.GetCell(33).CellStyle = headStyle;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = cellStyle;

                            headerRow1.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow1.GetCell(i).CellStyle = cellStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 280);
                            if (i > 7 && i < oldColumnNames.Length - 10)
                            {
                                sheet.SetColumnWidth(i, (arrColWidth[4] + 1) * 280);
                            }

                        }

                        headerRow.GetCell(3).SetCellValue("上月");
                        headerRow.GetCell(9).SetCellValue("本月");
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        //IRow row1 = sheet.CreateRow(0);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 3));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 9));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 10, 35));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 35, 36));
                        //row1.CreateCell(0).SetCellValue("人员信息档案表");
                        //row1.GetCell(0).CellStyle = headStyle;

                        ////设置第一列宽度
                        //sheet.SetColumnWidth(0, 12 * 256);
                        //row1.Height = 28 * 30;

                    }
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容

                if (j > 0)
                {
                    //int不能用于计算
                    decimal jj = j;
                    if ((jj - 33) % 35 == 0)
                    {
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 30));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 31, 33));
                        IRow dataRow1 = sheet.CreateRow(rowIndex);
                        for (var i = 0; i < 35; i++)
                        {
                            dataRow1.CreateCell(i).SetCellValue(" ");
                            dataRow1.GetCell(i).CellStyle = cellStyle;
                        }
                        ICell newCell = dataRow1.GetCell(0);
                        newCell.SetCellValue("申明：此表登记务工人员为我单位本月在该工程全部出勤人数，出勤情况属实；我单位已将此表向全体务工人员公示，均无异议。");
                        newCell.CellStyle = cellStyle;
                        ICell newCell2 = dataRow1.GetCell(31);
                        newCell2.SetCellValue("总计");
                        newCell2.CellStyle = cellStyle;
                        ICell newCell3 = dataRow1.GetCell(34);
                        newCell3.SetCellValue(totalCount.ToString());
                        newCell3.CellStyle = cellStyle;
                        totalCount = 0;
                        rowIndex++;
                    }
                }
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    if (i == oldColumnNames.Length - 1)
                    {
                        totalCount += Convert.ToInt64(drValue);
                    }
                    if (i < oldColumnNames.Length - 1 && i >= 3)
                    {
                        int val = Convert.ToInt32(drValue);
                        if (val == 0)
                        {
                            drValue = string.Empty;
                        }
                        else
                        {
                            drValue = "√";
                        }
                    }
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            //int intV = 0;
                            //int.TryParse(drValue, out intV);
                            //newCell.SetCellValue(intV);
                            //newCell.CellStyle = cellStyle;
                            //break;
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            //double doubV = 0;
                            //double.TryParse(drValue, out doubV);
                            //newCell.SetCellValue(doubV);
                            //newCell.CellStyle = cellStyle;
                            //break;
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }

            //最后一行
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 30));
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 31, 33));
            IRow dataRow11 = sheet.CreateRow(rowIndex);
            for (var i = 0; i < 35; i++)
            {
                dataRow11.CreateCell(i).SetCellValue(" ");
                dataRow11.GetCell(i).CellStyle = cellStyle;
            }

            ICell newCell1 = dataRow11.GetCell(0);
            newCell1.SetCellValue("申明：此表登记务工人员为我单位本月在该工程全部出勤人数，出勤情况属实；我单位已将此表向全体务工人员公示，均无异议。");
            ICell newCell21 = dataRow11.GetCell(31);
            newCell21.SetCellValue("总计");
            ICell newCell31 = dataRow11.GetCell(34);
            newCell31.SetCellValue(totalCount.ToString());

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion


        #region 工资发放表
        public static void ExportByEmployeeEntryExit4(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream4(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream4(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.1);
            sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B________公司务工人员（含队长、班组长、农民工）工资发放表&B\n "+HSSFHeader.FontSize((short)10)+"班组名称：&U  " + team.Split(',')[0] + "  &U ";
            sheet.Header.Left = "              \n\n项目名称（全称）：__________";
            sheet.Header.Right = "               \n\n " + team.Split(',')[1] + "年 " + team.Split(',')[2] + "月";
            sheet.Footer.Left = "申明：此表登记务工人员为我单位\n本月在该工程全部人数；工资结算、\n支付、领取情况属实，均系本人签字。  ";
            sheet.Footer.Center = "班组长签字：_____________ ；用工企业劳务员签字：_______________，\n用工企业项目负责人（授权队长）签字：____________；填表时间：____________ \n 第&P页,共&N页";
            sheet.Footer.Right = "用工企业盖章：_________       \n           \n            ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        IRow headerRow1 = sheet.CreateRow(1);
                   
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 5, 5));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 10, 10));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 11, 11));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 12, 12));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 13, 13));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 6, 9));
                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = cellStyle;

                            headerRow1.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow1.GetCell(i).CellStyle = cellStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 280);
                            if (i >= 6 && i < 10)
                            {
                                sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 380);
                            }
                            if (i == 13)
                            {
                                sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 400);
                            }
                        }
                        headerRow.GetCell(6).SetCellValue("扣款部分");
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        //IRow row1 = sheet.CreateRow(0);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6));
                        //row1.CreateCell(0).SetCellValue("_____________公司务工人员（含队长、班组长、农民工）花名册");
                        //row1.GetCell(0).CellStyle = headStyle;

                        ////设置第一列宽度
                        //sheet.SetColumnWidth(0, 12 * 256);
                        //row1.Height = 28 * 30;

                    }
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 本周进场务工人员情况
        public static void ExportByEmployeeEntryExit5(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream5(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream5(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.3);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.2);
            sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B________项目人员变更周报表（进场情况）&B\n"+ HSSFHeader.FontSize((short)10)+"班组名称：&U  " + team.Split(',')[0] + "  &U    \n"  + " 本周离场务工人员总数:" + team.Split(',')[4];
            sheet.Header.Left = "              \n\n劳务（专业）分包单位：___________    \n 本周进场务工人员总数:" + team.Split(',')[3];
            sheet.Header.Right = "               \n\n日期：" + team.Split(',')[1] + "至 " + team.Split(',')[2] + "\n 本周现场务工人员总数：" + team.Split(',')[5];
            sheet.Footer.Left = "申明：此表登记务工人员\n为我单位在该工程本周全\n部进场人数，情况属实。\n             ";
            sheet.Footer.Center = "班组长签字：______________ ；用工企业劳务员签字：_____________，\n用工企业项目负责人（授权队长）签字：________；填表时间：________ \n 第&P页,共&N页";
            sheet.Footer.Right = "用工企业盖章：_________       \n           \n            ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 270);
                            if (i == oldColumnNames.Length - 1)
                            {
                                sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 500);
                            }
                        }
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7));
                        row1.CreateCell(0).SetCellValue("本周进场务工人员情况");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 28 * 30;

                    }
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 本周离场务工人员情况
        public static void ExportByEmployeeEntryExit6(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream6(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream6(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.2);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.2);
            sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B________项目人员变更月报表（离场情况）&B  \n" + HSSFHeader.FontSize((short)10) + "班组名称：&U  " + team.Split(',')[0] + "  &U    ";
            sheet.Header.Left = "              \n\n劳务（专业）分包单位：___________  ";
            sheet.Header.Right = "              \n \n日期：" + team.Split(',')[1] + "至 " + team.Split(',')[2];
            sheet.Footer.Left = "申明：此表登记务工人员\n为我单位在该工程本周全\n部离场人数，情况属实。\n             ";
            sheet.Footer.Center = "班组长签字：______________ ；用工企业劳务员签字：_____________，\n用工企业项目负责人（授权队长）签字：________；填表时间：________ \n 第&P页,共&N页";
            sheet.Footer.Right = "用工企业盖章：_________       \n           \n            ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 250);
                            //if (i == oldColumnNames.Length - 1)
                            //{
                            //    sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 500);
                            //}
                        }
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7));
                        row1.CreateCell(0).SetCellValue("本周离场务工人员情况");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 28 * 30;

                    }
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        /// <summary>读取excel   
        /// 默认第一行为表头，导入第一个工作表
        /// </summary>   
        /// <param name="strFileName">excel文档路径</param>   
        /// <returns></returns>   
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();
                            switch (cell.CellType)
                            {
                                case CellType.BLANK:
                                    dataRow[j] = null;
                                    break;
                                case CellType.BOOLEAN:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.STRING:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.ERROR:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.FORMULA:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            #endregion
            return table;
        }

        public static void PrintSetUp(ISheet sheet)
        {
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)0.5);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.5);
            sheet.SetMargin(MarginType.BottomMargin, (double)0.5);
            //打印份数
            sheet.PrintSetup.Copies = 1;
            //单色打印
            sheet.PrintSetup.NoColor = true;
            //横向
            sheet.PrintSetup.Landscape = true;
            sheet.PrintSetup.PaperSize = (short)PaperSize.A4;
            //页高
            sheet.PrintSetup.FitHeight = 1;
            //页宽
            sheet.PrintSetup.FitWidth = 1;
            sheet.IsPrintGridlines = true;

            //是否自适应界面
            sheet.FitToPage = false;
            //设置打印标题
            //workbook.SetRepeatingRowsAndColumns(0, 0, 5, 0, 5);
        }
    }
}
